﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetMyDraft]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetMyDraft];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetMyDraft]
    @StaffName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @staffid int

SELECT @staffid = staff_id FROM dbo.uds_staff WHERE staff_name = @StaffName;
IF @@rowcount <> 1
BEGIN
	raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

SELECT
        a.Doc_ID,
        a.a Title,
        b.Doc_Builder_ID,
        CONVERT(nvarchar(10),Doc_Added_Date,120) AS Short_Doc_Added_Date,
        b.Doc_Status,
        b.Flow_ID,
        b.Step_ID,
        c.realname AS DocBuilder,
        d.flow_name AS FlowName,
        N'草稿' AS status,
        e.step_name AS StepName
    FROM 
        dbo.uds_flow_Style_Data a,
        dbo.uds_flow_document b,
        dbo.uds_staff c,
        dbo.uds_flow d,
        dbo.uds_flow_step e
    WHERE
        a.Doc_ID = b.Doc_ID 
        and b.flow_id = e.flow_id
        and b.step_id = e.step_id
        and b.flow_id = d.flow_id
        and b.doc_builder_id = c.staff_id
        and c.staff_id = @staffid    
        and b.isrunning = 0
    ORDER BY
        b.doc_added_date DESC;

END
GO